/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Handlers;

import gui.Main;
import ObjectClass.Certificate;
import ObjectClass.Driver;
import ObjectClass.DriverCertificate;
import ObjectClass.SQLCommunication;
import ObjectClass.Vehicle;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Jeppe
 */
public class DriverHandler {

    private final static String TABEL = "Driver";
    private SQLCommunication sqlc;
    private DriverCertificateHandler driverCertificateHandler;
    private ArrayList<Driver> driverList;

    public DriverHandler(Main main) {
        this.sqlc = main.getSql();
        driverList = new ArrayList<>();
    }

    private void instantierDriverList() {
        driverList.clear();
        Driver driver = null;
        int phoneNo;
        String fName, lName;

        String sql = "SELECT * FROM " + TABEL;
        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            while (rs.next()) {
                phoneNo = rs.getInt("PhoneNo");
                fName = rs.getString("fName");
                lName = rs.getString("lName");
                driver = new Driver(phoneNo, fName, lName);
                driverList.add(driver);
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af DriverList");
        }
    }

    public ArrayList<Driver> getDriverList() {
        instantierDriverList();
        return driverList;
    }

    public boolean tjekDriverByPhoneNo(int phoneNo) {
        boolean exists = false;
        String sql;
        sql = "SELECT * FROM " + TABEL + " WHERE phoneNo= " + phoneNo;
        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            while (rs.next()) {
                exists = true;
            }
        } catch (SQLException ex) {
            System.out.println("");
        }
        return exists;
    }

    public Driver getDriver(int phoneNo) {
        Driver aDriver = null;
        String fName = "";
        String lName = "";
        String sql;
        sql = "SELECT * FROM " + TABEL + " WHERE phoneNo= " + phoneNo;
        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            if (rs.next()) {
                phoneNo = rs.getInt("PhoneNo");
                fName = rs.getString("fName");
                lName = rs.getString("lName");
                aDriver = new Driver(phoneNo, fName, lName);
            }
        } catch (SQLException ex) {
            System.out.println("Driver med tlfnr " + phoneNo + " blev ikke fundet i databasen. (getDriver i driverhandler)");
        }
        return aDriver;
    }

    public ArrayList<Driver> getDriverListByCertificateNY(boolean ADRCertificate, boolean craneCertificate, boolean EUCertificate, int dd, int mm, int YYYY) {
        ArrayList<Driver> demandDriverList = new ArrayList<>();
        String sql = "SELECT  PhoneNo, FName, LName "
                + "FROM  driver "
                + "WHERE driver.PhoneNo NOT IN (SELECT task.DriverphoneNo "
                + "FROM task "
                + "WHERE task.DD = " + dd + " and task.MM = " + mm + " and task.YYYY = " + YYYY + ") ";
        if (ADRCertificate) {
            sql = "SELECT  PhoneNo, FName, LName "
                    + "FROM  driver "
                    + "WHERE driver.PhoneNo NOT IN (SELECT task.DriverphoneNo "
                    + "FROM task "
                    + "WHERE task.DD = " + dd + " and task.MM = " + mm + " and task.YYYY = " + YYYY + ") "
                    + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                    + "FROM drivercertificate "
                    + "WHERE CertificateID = 1) ";
            if (craneCertificate) {
                sql = "SELECT  PhoneNo, FName, LName "
                        + "FROM  driver "
                        + "WHERE driver.PhoneNo NOT IN (SELECT task.DriverphoneNo "
                        + "FROM task "
                        + "WHERE task.DD = " + dd + " and task.MM = " + mm + " and task.YYYY = " + YYYY + ") "
                        + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                        + "FROM drivercertificate "
                        + "WHERE CertificateID = 1) "
                        + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                        + "FROM drivercertificate "
                        + "WHERE CertificateID = 2)";
                if (EUCertificate) {
                    sql = "SELECT  PhoneNo, FName, LName "
                            + "FROM  driver "
                            + "WHERE driver.PhoneNo NOT IN (SELECT task.DriverphoneNo "
                            + "FROM task "
                            + "WHERE task.DD = " + dd + " and task.MM = " + mm + " and task.YYYY = " + YYYY + ") "
                            + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                            + "FROM drivercertificate "
                            + "WHERE CertificateID = 1) "
                            + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                            + "FROM drivercertificate "
                            + "WHERE CertificateID = 2) "
                            + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                            + "FROM drivercertificate "
                            + "WHERE CertificateID = 3)";
                }
            }
        } else if (craneCertificate) {
            sql = "SELECT  PhoneNo, FName, LName "
                    + "FROM  driver "
                    + "WHERE driver.PhoneNo NOT IN (SELECT task.DriverphoneNo "
                    + "FROM task "
                    + "WHERE task.DD = " + dd + " and task.MM = " + mm + " and task.YYYY = " + YYYY + ") "
                    + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                    + "FROM drivercertificate "
                    + "WHERE CertificateID = 2) ";
            if (EUCertificate) {
                sql = "SELECT  PhoneNo, FName, LName "
                        + "FROM  driver "
                        + "WHERE driver.PhoneNo NOT IN (SELECT task.DriverphoneNo "
                        + "FROM task "
                        + "WHERE task.DD = " + dd + " and task.MM = " + mm + " and task.YYYY = " + YYYY + ") "
                        + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                        + "FROM drivercertificate "
                        + "WHERE CertificateID = 2) "
                        + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                        + "FROM drivercertificate "
                        + "WHERE CertificateID = 3)";
            }
        } else if (EUCertificate) {
            sql = "SELECT  PhoneNo, FName, LName "
                    + "FROM  driver "
                    + "WHERE driver.PhoneNo NOT IN (SELECT task.DriverphoneNo "
                    + "FROM task "
                    + "WHERE task.DD = " + dd + " and task.MM = " + mm + " and task.YYYY = " + YYYY + ") "
                    + "AND driver.PhoneNo IN (SELECT DriverPhoneNo "
                    + "FROM drivercertificate "
                    + "WHERE CertificateID = 3) ";
        }

        ResultSet rs = sqlc.select(TABEL, sql);
        int phoneNo;
        String fName, lName;
        try {
            while (rs.next()) {
                phoneNo = rs.getInt("PhoneNo");
                fName = rs.getString("fName");
                lName = rs.getString("lName");
                Driver driver = new Driver(phoneNo, fName, lName);
                demandDriverList.add(driver);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DriverHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return demandDriverList;
    }
}
